import sqlite3
import os
import yaml

def read_database():
    # 确保数据库文件存在
    db_path = "db/rosbuiler.db"
    if not os.path.exists(db_path):
        print(f"Database file not found at {db_path}")
        return

    try:
        # 连接到数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 获取所有表名
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()

        for table in tables:
            table_name = table[0]
            print(f"\nTable: {table_name}")
            print("-" * 50)

            # 获取表结构
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns = cursor.fetchall()
            column_names = [col[1] for col in columns]
            print("Columns:", ", ".join(column_names))

            # 获取表数据
            cursor.execute(f"SELECT * FROM {table_name}")
            rows = cursor.fetchall()
            
            print(f"\nTotal rows: {len(rows)}")
            for row in rows[:5]:  # 只显示前5行
                print(row)

    except sqlite3.Error as e:
        print(f"SQLite error: {e}")
    finally:
        if conn:
            conn.close()

def read_git_src():
    # 确保数据库文件存在
    db_path = "db/rosbuiler.db"
    if not os.path.exists(db_path):
        print(f"Database file not found at {db_path}")
        return

    try:
        # 连接到数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # 获取所有表名
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        # tables = cursor.fetchall()

        table_name= "ros_repos"
        
        # 获取表数据
        cursor.execute(f"SELECT release_src_name,release_src_url FROM {table_name}")
        rows = cursor.fetchall()
        
        print(f"\nTotal rows: {len(rows)}")
        for row in rows[:5]:  # 只显示前5行
            print(row)
            
        return rows  # Return all rows, not just one

    except sqlite3.Error as e:
        print(f"SQLite error: {e}")
    finally:
        if conn:
            conn.close()

def builder_vcs_yaml():
    rows = read_git_src()
    repos_dict = {"repositories": {}}
    
    for row in rows:
        name = row[0].strip()  # release_src_name as repo identifier
        url = row[1].strip() + ".git"  # release_src_url
        branch = "main"
        
        repos_dict["repositories"][name] = {
            "type": "git",
            "url": url,
            "version": branch
        }
    
    # Convert to YAML and save to file
    with open("repos.yaml", "w") as f:
        yaml.dump(repos_dict, f, default_flow_style=False)
    
    return repos_dict

if __name__ == "__main__":
    # read_database()
    result = builder_vcs_yaml()
    print(yaml.dump(result, default_flow_style=False))
